from datetime import datetime
import requests
import pymysql
import time


# 国内股票
# 主程序
def stockstart():
    try:
        print(datetime.now())
        datas = getsql('SELECT * FROM touzi_ssqygpbm;')
        i = 0
        for data in datas:
            if data[2] != 'SESL':
                stocknum = data[1] + data[2]
                print(stocknum)
                time.sleep(2)
                s = getdata(stocknum).replace('";\n',
                                              '').split('="')[1]
                s2 = s.split('~')
                sql = makesql(s2)
                savesql(sql)
                i += 1
        log = "更新" + str(i) + "条入库touzi_ssqygpxx_copy"
        saveLog('INSERT INTO behc_log (name, log, update_time) VALUES ("国内股票", "' + log + '", "' + datetime.strftime(datetime.now(), '%Y-%m-%d %H:%M:%S') + '")')
        print(datetime.now())
    except Exception as e:
        print(e)


# 获取所需股票
def getsql(sql):
    con = None
    cur = None
    try:
        con = pymysql.connect(host="192.168.155.141",
                              port=3306,
                              user="root",
                              passwd="fD@LCVby2WJI",
                              database="behc_python",
                              charset='utf8')
        if con:
            print("链接成功")
            # 获取操作数据库的游标
            cur = con.cursor()
            # 执行sql语句
            cur.execute(sql)
            datas = cur.fetchall()
        else:
            print("链接失败")
    except Exception as e:
        print(e)
    finally:
        if con:
            if cur:
                cur.close()
            con.close()
    return datas


# 爬取股票信息
def getdata(stocknum):
    try:
        url = 'https://qt.gtimg.cn/q=' + stocknum
        r = requests.get(url=url)

        print(r,'获取数据')
        s = r.text
        print(s,'sssssssssssssssssssssss')
    except Exception as e:
        print(e)
        print(stocknum, '爬取失败')
    return s


# 生成SQL
def makesql(s):
    print(len(s), s[0])
    sql = 'INSERT INTO touzi_ssqygpxx_copy (s0, s1, s2, s3, s4, s5, s31, s32, s33, s34, s36, s37, s38, s39, s43, s44, s45, s46, s72, s75) VALUES ('
    if s[0] == '100':
        l = [
            0, 1, 2, 3, 4, 5, 31, 32, 33, 34, 36, 37, 38, 39, 43, 44, 45, 47,
            69
        ]
    else:
        l = [
            0, 1, 2, 3, 4, 5, 31, 32, 33, 34, 36, 37, 38, 39, 43, 44, 45, 46,
            72
        ]
    for i in l:
        sql += '"' + s[i] + '",'
    sql += '"' + str(datetime.now().date()) + '");'
    return sql


# 数据入库
def savesql(sql):
    con = None
    cur = None
    result = '执行成功'
    try:
        con = pymysql.connect(host="192.168.155.141",
                              port=3306,
                              user="root",
                              passwd="fD@LCVby2WJI",
                              database="behc_python",
                              charset='utf8')
        if con:
            print("链接成功")
            # 获取操作数据库的游标
            cur = con.cursor()
            # 执行sql语句
            cur.execute(sql)
            con.commit()
        else:
            print("链接失败")
            print(sql)
    except Exception as e:
        result = e
    finally:
        if con:
            if cur:
                cur.close()
            con.close()
    print(result)


# 日志入库
def saveLog(sql):
    con = None
    cur = None
    result = '执行成功'
    try:
        con = pymysql.connect(host="192.168.155.141",
                              port=3306,
                              user="root",
                              passwd="fD@LCVby2WJI",
                              database="behc_python",
                              charset='utf8')
        if con:
            print("链接成功")
            # 获取操作数据库的游标
            cur = con.cursor()
            # 执行sql语句
            cur.execute(sql)
            con.commit()
        else:
            print("链接失败")
            print(sql)
    except Exception as e:
        result = e
    finally:
        if con:
            if cur:
                cur.close()
            con.close()
    print(result)


stockstart()

